"""
Case Type   : fullsql
Case Name   : 数据准备
Create At   : 2022/01/25
Owner       : zhao-pengcheng-g
Description :
    1.检查参数，修改配置:enable_copy_server_files=on
    2.检查参数，修改配置:instr_unique_sql_count=100000
    3.创建用户，创建表
    4.导入数据
    5.设置track_stmt_session_slot为1000
    6.设置log_min_duration_statement=0，使用普通用户执行query
    7.查询系统表
    8.查询Full SQL
    9.查询系统表中在full_sql数据中而不在slow_sql中的数据
    10.环境清理
Expect      :
    1.修改参数成功
    2.修改参数成功
    3.创建成功
    4.导入成功
    5.设置成功
    6.设置成功，查询成功
    7.1条记录
    8.1条记录
    9.没有数据
    10.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0003 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_partsupp_fullsql_0003'
        self.user_name = 'u_fullsql_0003'
        self.table_path = os.path.join(r'/home',
                                       self.userNode.ssh_user, 'copy_tables')

        text = '---step1:检查参数，修改配置:enable_copy_server_files=on,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_copy = 'enable_copy_server_files'
        self.show_copy_cmd = f'show {self.config_copy};'
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        self.copy_default = check_copy.splitlines()[-2].strip()
        if 'on' != self.copy_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}=on')
            self.assertTrue(result, '参数修改失败')
        chk_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(chk_copy_msg)
        self.common.equal_sql_mdg(chk_copy_msg, self.config_copy, 'on',
                                  '(1 row)', flag='1')
        text = '---step2:检查参数，修改配置:instr_unique_sql_count=100000,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_instr = 'instr_unique_sql_count'
        self.show_instr_cmd = f'show {self.config_instr};'
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        self.instr_default = check_instr.splitlines()[-2].strip()
        if '100000' != self.instr_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}=100000')
            self.assertTrue(result, '参数修改失败')
        chk_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(chk_instr_msg)
        self.common.equal_sql_mdg(chk_instr_msg, self.config_instr, '100000',
                                  '(1 row)', flag='1')

        text = '---step3:清理历史记录,创建用户，创建表,expect;成功---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
                    drop user if exists {self.user_name} cascade;
                    create user {self.user_name} sysadmin \
                    password '{macro.COMMON_PASSWD}'; '''
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('reset_unique_sql' and '(1 row)',
                      sql_msg, '执行失败:' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                      self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)
        text = '---step3.2:切换用户,创建partsupp表,expect:创建成功 ---'
        self.logger.info(text)
        create_partsupp = f'''set role {self.user_name} \
                            password '{macro.COMMON_PASSWD}';
                            set current_schema to public;
                            drop table if exists {self.table_name};
                            create table {self.table_name}
                            (
                            ps_partkey     bigint not null, 
                            ps_suppkey     bigint not null, 
                            ps_availqty    bigint not null, 
                            ps_supplycost  decimal(15,2)  not null, 
                            ps_comment     varchar(199) not null
                            )
                            partition by range(ps_availqty)
                            (
                            partition ps_availqty_1 values less than(1000),
                            partition ps_availqty_2 values less than(2000),
                            partition ps_availqty_3 values less than(3000),
                            partition ps_availqty_4 values less than(4000),
                            partition ps_availqty_5 values less than(5000),
                            partition ps_availqty_6 values less than(6000),
                            partition ps_availqty_7 values less than(7000),
                            partition ps_availqty_8 values less than(8000),
                            partition ps_availqty_9 values less than(9000),
                            partition ps_availqty_10 values less than(10000)
                            );'''
        msg_partsupp = self.sh_primy.execut_db_sql(create_partsupp)
        self.logger.info(msg_partsupp)
        self.assertTrue(msg_partsupp.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      msg_partsupp, '执行失败:' + text)

        text = '---step4:导入数据，expect:导入成功---'
        self.logger.info(text)
        mkdir_cmd = f'rm -rf {self.table_path};' \
                    f'mkdir {self.table_path}'
        self.userNode.sh(mkdir_cmd)
        self.common.scp_file(self.userNode, 'partsupp.tbl',
                             self.table_path)
        partsupp_path = os.path.join(self.table_path, 'partsupp.tbl')
        copy_cmd = f'''set role {self.user_name} \
                            password '{macro.COMMON_PASSWD}';
                            copy {self.table_name} from '{partsupp_path}' \
                            using delimiters '|';
                            analyze;'''
        self.logger.info(copy_cmd)
        copy_msg = self.sh_primy.execut_db_sql(copy_cmd)
        self.logger.info(copy_msg)
        self.assertTrue(self.constant.SET_SUCCESS_MSG and
                        self.constant.copy_success_msg and
                        self.constant.ANALYZE_SUCCESS_MSG,
                        '执行失败:' + text)

    def test_fullsql(self):
        text = '---step5:设置track_stmt_session_slot为1000,expect:设置成功---'
        self.logger.info(text)
        self.config_slot = 'track_stmt_session_slot'
        self.show_slot_cmd = f'show {self.config_slot};'
        check_slot = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(check_slot)
        self.slot_default = check_slot.splitlines()[-2].strip()
        if '1000' != self.slot_default:
            guc_slot = self.sh_primy.execute_gsguc('reload',
                                                   self.constant.GSGUC_SUCCESS_MSG,
                                                   f'{self.config_slot}=1000')
            self.assertTrue(guc_slot, '执行失败:' + text)
        chk_slot_msg = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(chk_slot_msg)
        self.common.equal_sql_mdg(chk_slot_msg, self.config_slot, '1000',
                                  '(1 row)', flag='1')

        text = '---step6:设置log_min_duration_statement=0，' \
               '使用普通用户执行query,expect:成功---'
        self.logger.info(text)
        sql_cmd = f"set log_min_duration_statement = 0;" \
                  f"set role {self.user_name} password '{macro.COMMON_PASSWD}';" \
                  f"set current_schema to public;" \
                  f"select * from {self.table_name} order by 1,2,3,4,5 limit 1;" \
                  f"select pg_sleep(60);"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count(self.constant.SET_SUCCESS_MSG) == 3,
                        '执行失败:' + text)
        self.assertIn('(1 row)', sql_msg, '执行失败:' + text)

        text = '---step7:查询系统表,expect:1条记录---'
        self.logger.info(text)
        sql_cmd = f"""select * from statement_history 
                where unique_query_id=(select unique_sql_id 
                    from dbe_perf.statement 
                    where query like 'select * from {self.table_name} %' \
                        and user_name = '{self.user_name}');"""
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertIn('(1 row)', sql_msg, '执行失败:' + text)

        text = '---step8:查询Full SQL,expect:1条记录---'
        self.logger.info(text)
        fullsql_cmd = f'''select * from 
                dbe_perf.get_global_full_sql_by_timestamp\
                (clock_timestamp() -300,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like 'select * from {self.table_name} %' 
                    and user_name = '{self.user_name}');'''
        fullsql_msg = self.sh_primy.execut_db_sql(fullsql_cmd,
                                                  dbname='postgres')
        self.logger.info(fullsql_msg)
        self.assertIn('(1 row)', fullsql_msg, '执行失败:' + text)

        text = '---step9:查询系统表中slow_sql,expect:没有记录---'
        self.logger.info(text)
        minus_cmd = f'''select * from 
                dbe_perf.get_global_full_sql_by_timestamp\
                (clock_timestamp() - 300,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like 'select * from {self.table_name} %' 
                    and user_name = '{self.user_name}') 
            minus all 
            (select * from 
                dbe_perf.get_global_slow_sql_by_timestamp\
                (clock_timestamp() - 300,clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like 'select * from {self.table_name} %' 
                    and user_name = '{self.user_name}'));'''
        minus_msg = self.sh_primy.execut_db_sql(minus_cmd, dbname='postgres')
        self.logger.info(minus_msg)
        self.assertIn('(0 rows)', minus_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step10:环境清理，expect:成功---'
        self.logger.info(text)
        text = '---检查参数，恢复原值:enable_copy_server_files---'
        self.logger.info(text)
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        current = check_copy.splitlines()[-2].strip()
        if self.copy_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}={self.copy_default}')
            self.assertTrue(result, '参数恢复失败')
        show_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(show_copy_msg)

        text = '---检查参数，恢复原值:instr_unique_sql_count---'
        self.logger.info(text)
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        current = check_instr.splitlines()[-2].strip()
        if self.instr_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}={self.instr_default}')
            self.assertTrue(result, '参数恢复失败')
        show_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(show_instr_msg)

        text = '---检查参数，恢复原值:track_stmt_session_slot---'
        self.logger.info(text)
        check_slot = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(check_slot)
        current = check_slot.splitlines()[-2].strip()
        if self.slot_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_slot}={self.slot_default}')
            self.assertTrue(result, '参数恢复失败')
        show_slot_msg = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(show_slot_msg)

        text = '---删除表，删除用户,删除文件夹---'
        self.logger.info(text)
        drop_cmd = f'''drop owned by {self.user_name} cascade;
                       drop user {self.user_name};'''
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        rm_cmd = f'rm -rf {self.table_path};'
        self.rootNode.sh(rm_cmd)
        self.assertIn(self.constant.DROP_OWNED_SUCCESS and
                      self.constant.DROP_ROLE_SUCCESS_MSG,
                      drop_msg, '执行失败:' + text)
        self.assertFalse(os.path.exists(self.table_path), '执行失败:' + text)
        self.common.equal_sql_mdg(show_copy_msg, self.config_copy,
                                  self.copy_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_instr_msg, self.config_instr,
                                  self.instr_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_slot_msg, self.config_slot,
                                  self.slot_default, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0003 finish---')
